package com.zaofan.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;

import com.zaofan.bean.Account;
import com.zaofan.bean.NewUser;
import com.zaofan.bean.OrderDetail;
import com.zaofan.bean.OrderInfo;
import com.zaofan.bean.Prepaid;
import com.zaofan.bean.ProductList;
import com.zaofan.service.OrderDetailService;

/**
 * Excel文件操作工具
 * 
 * @author dxt
 * 
 */
public class ExcelUtils {

	/**
	 * 
	 * @param siteInfos
	 *            数据
	 * @param out
	 *            输出流
	 */
	public static void Order2Excel(List<OrderInfo> mobiles, HttpServletRequest request, HttpServletResponse response) {
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet("订单数据");
		HSSFRow firstrow = sheet.createRow(0);
		HSSFCell[] firstcell = new HSSFCell[1];
		String[] names = new String[13];
		names[0] = "手机号码";
		for (int j = 0; j < 1; j++) {
			firstcell[j] = firstrow.createCell(j);
			firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
		}
		HSSFCell cell = null;
		for (int i = 0; i < mobiles.size(); i++) {
			OrderInfo order = mobiles.get(i);
			HSSFRow row = sheet.createRow(i + 1);
			cell = row.createCell(0);
			cell.setCellValue(order.getAccountMobile());
		}
		try {
			String filename = "mobiles.xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public static String getDate(Date date) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		return df.format(date);
	}

	public static String formatDate(Date date) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return df.format(date);
	}

	/**
	 * 解析网站信息Excel文件
	 *
	 * @param file
	 * @return
	 */
	// public static DataSet Excel2Users(InputStream inputStream) {
	// SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
	// List<User> users = new ArrayList<User>();
	// ExcelSet set = new ExcelSet();
	// try {
	// HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
	// HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
	// if (hssfSheet == null)
	// return new DataSet();
	// int row = hssfSheet.getLastRowNum();
	// User user = null;
	// set.setTotal(row);
	// for (int j = 1; j <= row; j++) {
	// HSSFRow hssfRow = hssfSheet.getRow(j);
	// if (hssfRow == null) {
	// set.getError().add(new ExcelError("内容为空!!", j));
	// continue;
	// }
	// try {
	// user = new User();
	// String mobile = getValue(hssfRow.getCell(0));
	// if (WebUtils.isMobileNO(mobile)) {
	// user.setMobile(mobile);
	// } else {
	// set.getError().add(new ExcelError("手机格式出错(忽略导入)", j));
	// continue;
	// }
	// String passwrod = getValue(hssfRow.getCell(1));
	// if (WebUtils.isEmpty(passwrod)) {
	// set.getError().add(new ExcelError("用户密码为空(忽略导入)", j));
	// continue;
	// } else {
	// user.setPassword(passwrod);
	// }
	// user.setName(getValue(hssfRow.getCell(2)));
	// user.setNickName(getValue(hssfRow.getCell(3)));
	// String birthday = getValue(hssfRow.getCell(4));
	// try {
	// df.parse(birthday);
	// user.setBirthday(birthday);
	// } catch (Exception exception) {
	// set.getError().add(new ExcelError("用户生日格式出错(用户导入成功,生日字段为空)!!", j));
	// user.setBirthday("");
	// }
	// user.setSex(getValue(hssfRow.getCell(5)));
	// String email = getValue(hssfRow.getCell(6));
	// if (WebUtils.isEmail(email)) {
	// set.getError().add(new ExcelError("用户邮箱格式出错(用户导入成功,邮箱字段为空)!!", j));
	// user.setEmail(email);
	// } else {
	// user.setEmail("");
	// }
	// user.setCardNum(getValue(hssfRow.getCell(7)));
	// user.setQq(getValue(hssfRow.getCell(8)));
	// try {
	// user.setType(Integer.valueOf(getValue(hssfRow.getCell(9))));
	// } catch (Exception e) {
	// user.setType(0);
	// e.printStackTrace();
	// }
	// users.add(user);
	// } catch (Exception e) {
	// e.printStackTrace();
	// continue;
	// }
	// }
	// DataSet dataSet = new DataSet();
	// dataSet.users = users;
	// dataSet.excelSet = set;
	// return dataSet;
	// } catch (IOException e) {
	// e.printStackTrace();
	// }
	// return new DataSet();
	// }

	/**
	 * 读取Excel单元的内容
	 * 
	 * @param hssfCell
	 * @return
	 */
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell == null) {
			return "";
		}
		int type = hssfCell.getCellType();
		// 判断单元格内容数据类型
		if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
			// 单元格数据为Date类型
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				System.err.println(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()).toString());
				return HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()).toString();
			}
			// 单元格数据为电话号码等长数字字符串时进行格式化
			return String.valueOf(new DecimalFormat("#").format(hssfCell.getNumericCellValue()));
		} else if (type == HSSFCell.CELL_TYPE_BLANK || type == HSSFCell.CELL_TYPE_ERROR) {
			// 单元格数据为空或者读取出错
			return "";
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	public static void exportTomorrowList(List<ProductList> orders, HttpServletRequest request,
			HttpServletResponse response) {
		HSSFWorkbook hwb = new HSSFWorkbook();

		Map<String, HSSFSheet> sheets = new HashMap<>();

		HSSFSheet sheet = null;
		HSSFCell cell = null;
		HSSFRow row = null;
		for (ProductList item : orders) {
			if (sheets.containsKey(item.getShopName())) {
				sheet = sheets.get(item.getShopName());
				row = sheet.createRow(sheet.getLastRowNum() + 1);
				cell = row.createCell(0);
				cell.setCellValue(item.getProductName());
				cell = row.createCell(1);
				cell.setCellValue(item.getProductPrice().doubleValue());
				cell = row.createCell(2);
				cell.setCellValue(item.getProductCount());
			} else {
				sheet = hwb.createSheet(item.getShopName());
				HSSFCell[] firstcell = new HSSFCell[13];
				String[] names = new String[3];
				names[0] = "产品名称";
				names[1] = "产品价格";
				names[2] = "产品数量";
				HSSFRow firstrow = sheet.createRow(0);
				for (int j = 0; j < 3; j++) {
					firstcell[j] = firstrow.createCell(j);
					firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
				}
				row = sheet.createRow(sheet.getLastRowNum() + 1);
				cell = row.createCell(0);
				cell.setCellValue(item.getProductName());
				cell = row.createCell(1);
				cell.setCellValue(item.getProductPrice().doubleValue());
				cell = row.createCell(2);
				cell.setCellValue(item.getProductCount());
				sheets.put(item.getShopName(), sheet);
			}
		}
		try {
			String filename = "mobiles.xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void exportTomorrowListProduct(List<OrderInfo> orders, OrderDetailService orderDetailService,
			HttpServletRequest request, HttpServletResponse response) {
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet("订单数据");
		HSSFRow firstrow = sheet.createRow(0);

		HSSFCellStyle headlerstyle = hwb.createCellStyle(); // 样式对象
		headlerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		headlerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		headlerstyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);

		// 生成一个字体
		HSSFFont headerfont = hwb.createFont();
		headerfont.setColor(HSSFColor.BLACK.index);// HSSFColor.VIOLET.index
													// //字体颜色
		headerfont.setFontHeightInPoints((short) 12);
		headerfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
		headlerstyle.setFont(headerfont);

		HSSFCellStyle style = hwb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		// 生成一个字体
		HSSFFont font = hwb.createFont();
		font.setColor(HSSFColor.BLACK.index);// HSSFColor.VIOLET.index //字体颜色
		font.setFontHeightInPoints((short) 11);
		style.setFont(font);

		firstrow.setRowStyle(headlerstyle);
		HSSFCell[] firstcell = new HSSFCell[8];
		String[] names = new String[14];
		names[0] = "订货日期(订单号,订单总价格)";
		names[1] = "商品名称";
		names[2] = "数量";
		names[3] = "价格";
		names[4] = "收件人";
		names[5] = "地址";
		names[6] = "电话";
		names[7] = "送餐时间";
		for (int j = 0; j < 8; j++) {
			firstcell[j] = firstrow.createCell(j);
			firstcell[j].setCellStyle(headlerstyle);
			firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
		}
		sheet.setColumnWidth(0, 80 * 256);
		sheet.setColumnWidth(1, 50 * 256);
		sheet.setColumnWidth(2, 10 * 256);
		sheet.setColumnWidth(3, 10 * 256);
		sheet.setColumnWidth(4, 15 * 256);
		sheet.setColumnWidth(5, 30 * 256);
		sheet.setColumnWidth(6, 15 * 256);
		sheet.setColumnWidth(7, 30 * 256);

		HSSFCell cell = null;
		HSSFRow row = null;
		List<OrderDetail> orderDetails;
		int size = 0;
		int rowBegin = 1;
		int rowCount = 1;
		CellRangeAddress address;
		for (int i = 0; i < orders.size(); i++) {
			OrderInfo info = orders.get(i);
			if (info.getIsMuti() == 0) {
				orderDetails = orderDetailService.findByOrderId(info.getId());
			} else {
				orderDetails = orderDetailService.findByParentOrderId(info.getId());
			}
			rowBegin = rowCount;
			size = orderDetails.size();
			rowCount += size;
			for (int j = 0; j < size; j++) {
				row = sheet.createRow(sheet.getLastRowNum() + 1);
				if (j == 0) {
					// 生成订单号
					cell = row.createCell(0);
					cell.setCellValue(info.getOrderTime() + "(订单号:" + info.getOrderNo() + ")" + "(总价格:"
							+ (info.getTotalPrice().subtract(info.getPayFree())) + ",优惠价格:" + info.getFreePrice() + ")");
					cell.setCellStyle(style);
					address = new CellRangeAddress(rowCount - size, rowCount - 1, 0, 0);
					sheet.addMergedRegion(address);
				}
				cell = row.createCell(1);
				cell.setCellValue("(" + orderDetails.get(j).getShopName() + ")" + orderDetails.get(j).getProductName());
				cell.setCellStyle(style);
				cell = row.createCell(2);
				cell.setCellValue(orderDetails.get(j).getProductCount());
				cell.setCellStyle(style);
				cell = row.createCell(3);
				cell.setCellValue(orderDetails.get(j).getProductPrice().doubleValue());
				cell.setCellStyle(style);
				// 生成
				if (j == 0) {
					// 生成订单号
					cell = row.createCell(4);
					cell.setCellValue(info.getConsignee());
					cell.setCellStyle(style);
					address = new CellRangeAddress(rowCount - size, rowCount - 1, 4, 4);
					sheet.addMergedRegion(address);
					cell = row.createCell(5);
					cell.setCellValue(
							info.getBuilderName() + info.getFloorNo() + info.getDetail() + ",备注:" + info.getRemark());
					cell.setCellStyle(style);
					address = new CellRangeAddress(rowCount - size, rowCount - 1, 5, 5);
					sheet.addMergedRegion(address);
					cell = row.createCell(6);
					cell.setCellValue(info.getUserMobile());
					cell.setCellStyle(style);
					address = new CellRangeAddress(rowCount - size, rowCount - 1, 6, 6);
					sheet.addMergedRegion(address);
				}
				cell = row.createCell(7);
				cell.setCellValue(info.getSendDate() + "-" + info.getSendTime() + "");
				cell.setCellStyle(style);
			}
		}

		try {
			String filename = "order.xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}


	public static  void exportNewUser(String title, String[] thead, List<Account> datas, HttpServletResponse response){
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet(title);
		HSSFRow firstrow = sheet.createRow(0);
		HSSFCell[] firstcell = new HSSFCell[thead.length];
		for(int i=0;i<thead.length;i++){
			firstcell[i] = firstrow.createCell(i);
			firstcell[i].setCellValue(new HSSFRichTextString(thead[i]));
		}
		HSSFCell cell = null;
		for (int j = 0; j < datas.size(); j++) {
			HSSFRow row = sheet.createRow(j + 1);
            cell = row.createCell(0);
            cell.setCellValue(datas.get(j).getMobile());
            cell = row.createCell(1);
			String date = formatDate(datas.get(j).getRegeistDate());
			System.out.println(date+"---------------------打印的订单时间");
            cell.setCellValue(date);
		}
		try {
			String filename = title+".xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}


	public static  void exportNewOrder(String title,String[] thead,List<OrderInfo> datas,HttpServletResponse response){
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet(title);
		HSSFRow firstrow = sheet.createRow(0);
		HSSFCell[] firstcell = new HSSFCell[thead.length];
		for(int i=0;i<thead.length;i++){
			firstcell[i] = firstrow.createCell(i);
			firstcell[i].setCellValue(new HSSFRichTextString(thead[i]));
		}
		HSSFCell cell = null;
		for (int j = 0; j < datas.size(); j++) {
			HSSFRow row = sheet.createRow(j + 1);
			cell = row.createCell(0);
			cell.setCellValue(datas.get(j).getOrderNo());
			cell = row.createCell(1);
			cell.setCellValue(datas.get(j).getAccountMobile());
			cell = row.createCell(2);

			String date = formatDate(datas.get(j).getOrderTime());
			System.out.println(date+"---------------------打印的订单时间");
			cell.setCellValue(date);
			cell = row.createCell(3);
			cell.setCellValue(datas.get(j).getConsignee());
			cell = row.createCell(4);
			cell.setCellValue(String.valueOf(datas.get(j).getTotalPrice()));
			cell = row.createCell(5);
			cell.setCellValue(datas.get(j).getAreaName());
			cell = row.createCell(6);
			cell.setCellValue(datas.get(j).getDetail() + " " +  datas.get(j).getFloorNo());
		}
		try {
			String filename = title+".xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}



	public static void exportPrepaidDatas(List<Prepaid> datas, HttpServletRequest request,
			HttpServletResponse response) {
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet("充值卡");
		HSSFRow firstrow = sheet.createRow(0);
		HSSFCell[] firstcell = new HSSFCell[3];
		String[] names = new String[3];
		names[0] = "充值卡名称";
		names[1] = "卡号";
		names[2] = "卡密";
		for (int j = 0; j < 3; j++) {
			firstcell[j] = firstrow.createCell(j);
			firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
		}
		HSSFCell cell = null;
		for (int i = 0; i < datas.size(); i++) {
			Prepaid prepaid = datas.get(i);
			HSSFRow row = sheet.createRow(i + 1);
			cell = row.createCell(0);
			cell.setCellValue(prepaid.getName());
			cell = row.createCell(1);
			cell.setCellValue(prepaid.getCard());
			cell = row.createCell(2);
			cell.setCellValue(prepaid.getPassword());
		}
		try {
			String filename = "prepaid.xls";
			response.addHeader("Content-Disposition", "attachment;filename=" + filename);
			response.setContentType("application/octet-stream");
			hwb.write(response.getOutputStream());
			response.getOutputStream().close();
			System.out.println("数据导出成功");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}
